package cn.edu.scau.cmi.tangqingning.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.edu.scau.cmi.tangqingning.dao.UserDao;
import cn.edu.scau.cmi.tangqingning.model.Group;
import cn.edu.scau.cmi.tangqingning.model.Student;
import cn.edu.scau.cmi.tangqingning.model.User;
import cn.edu.scau.cmi.tangqingning.tools.DbConnection;

/**
 * 数据库访问层-关于管理员的信息操作的类<br>
 * 与数据库的交互操作所需要用到的方法<br>
 * 编写代码的时候要注意数据库的信息要与编号要一一对应
 *
 * @author 周华智
 *
 */
public class UserDaoImpl implements UserDao {
	private String create = "";

	/**
	 * 登陆管理员验证<br>
	 *通过传入的管理员信息 和t_manager表里的学生学号和密码对比，从而确定是否成功登录
	 *
	 * @param user 传入一个管理员的类
	 * @return flag flag=rue时登陆成功
	 */
	@Override
	public boolean isLogin(User user) {
		Connection conn = DbConnection.getConnection(create, false);
		boolean flag = false;
		String sql = "select * from t_manager where username=? and password=?";
		try {
			PreparedStatement pmst = conn.prepareStatement(sql);
			pmst.setString(1, user.getUsername());// 对传过来的用户名和密码进行封装
			pmst.setString(2, user.getPassword());
			// 第一个参数是该参数在语句中的索引。参数标记具有从 1 开始的编号。 通常指的是第一个“？”
			// 第二个参数是要对第一个参数设置的值
			ResultSet rs = pmst.executeQuery();// 把数据库响应的查询结果存放在ResultSet类对象中供我们使用
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 修改管理员的密码<br>
	 * 没有则返回a为0
	 *
	 * @param user 传入一个管理员对象
	 * @return a a=1时修改密码成功，没有则返回0
	 * @see cn.edu.scau.cmi.tangqingning.model.User
	 */

	@Override
	public int updateUserPassWord(User user) {
		Connection conn = DbConnection.getConnection(create, false);
		int a = 0;
		String sql = "update t_manager set password=? where username=?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getPassword());
			pstmt.setString(2, user.getUsername());
			a = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	/**
	 * 添加学生信息<br>
	 * 将一系列输入的学生信息存进stuperinfo表里面，返回true时添加成功
	 *
	 * @param stu 传入一个学生对象
	 * @return flag flag=true时添加学生信息成功
	 * @see cn.edu.scau.cmi.tangqingning.model.Student
	 */
	@Override
	public boolean addStudent(Student stu) {
		create = "create table " + "stuperinfo" + "(STID varchar(12) NOT NULL COMMENT '学号',"
				+ "STNAME varchar(20) NOT NULL COMMENT ' 姓名'," + " STSEX varchar(10) NOT NULL COMMENT '性别',"
				+ "STAGE varchar(10) NOT NULL COMMENT '年龄'," + "STTEL varchar(50) NOT NULL COMMENT '电话',"
				+ "STDEPT varchar(50) NOT NULL COMMENT '系别'," + "STADDRESS varchar(50) NOT NULL COMMENT '地址',"
				+ "STGROUP int(10) NOT NULL DEFAULT '0' COMMENT '小组'," + "PASSWORD varchar(12) NOT NULL COMMENT '密码',"
				+ "MARK varchar(200) NOT NULL DEFAULT '00000000000000000000"
				+ "0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
				+ "00000000000000000000000000000000000000000000000000000000000000000000000000000000',"
				+ "primary key(STID))" + "ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		boolean flag = false;
		String sql = "insert into stuperinfo(stId,stName,stSex,stAge,stTel,stDept,stAddress,password) values(?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setLong(1, stu.getStId());
			psmt.setString(2, stu.getStName());
			psmt.setString(3, stu.getStSex());
			psmt.setString(4, stu.getStAge());
			psmt.setLong(5, stu.getStTel());
			psmt.setString(6, stu.getStDept());
			psmt.setString(7, stu.getStAddress());
			psmt.setLong(8, stu.getPassword());
			int i = psmt.executeUpdate();// 返回值是一个整数，指示受影响的行数（即更新计数）
			if (i == 1) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 更新学生信息<br>
	 * 读取出原来的数据并且更新新的数据进数据库表stuperinfo
	 *
	 * @param stu 传入一个学生对象
	 * @return flag 如果成功读取出原来的数据并且更新新的数据进数据库，则为true; false 如果读取数据或者更新数据时发生错误，则为false
	 * @see cn.edu.scau.cmi.tangqingning.model.Student
	 */
	@Override
	public boolean updateStudent(Student stu) {
		Connection conn = DbConnection.getConnection(create, false);
		boolean flag = false;
		String sql = "update stuperinfo set stName=?,stSex=?,stAge=?,stTel=?,stDept=?,stAddress=?,stGroup=? where stId=? ";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, stu.getStName());
			psmt.setString(2, stu.getStSex());
			psmt.setString(3, stu.getStAge());
			psmt.setLong(4, stu.getStTel());
			psmt.setString(5, stu.getStDept());
			psmt.setString(6, stu.getStAddress());
			psmt.setString(7, stu.getStGroup());
			psmt.setLong(8, stu.getStId());

			int i = psmt.executeUpdate();
			if (i == 1) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 查询全体学生信息<br>
	 * 访问数据表stuperinfo，将全部学生信息显示出来，返回整个学生信息列表
	 *
	 * @return list 返回全体学生信息列表
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List StSelect() {
		create = "create table " + "stuperinfo" + "(STID varchar(12) NOT NULL COMMENT '学号',"
				+ "STNAME varchar(20) NOT NULL COMMENT ' 姓名'," + " STSEX varchar(10) NOT NULL COMMENT '性别',"
				+ "STAGE varchar(10) NOT NULL COMMENT '年龄'," + "STTEL varchar(50) NOT NULL COMMENT '电话',"
				+ "STDEPT varchar(50) NOT NULL COMMENT '系别'," + "STADDRESS varchar(50) NOT NULL COMMENT '地址',"
				+ "STGROUP int(10) NOT NULL DEFAULT '0' COMMENT '小组'," + "PASSWORD varchar(12) NOT NULL COMMENT '密码',"
				+ "MARK varchar(200) NOT NULL DEFAULT '00000000000000000000"
				+ "0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
				+ "00000000000000000000000000000000000000000000000000000000000000000000000000000000',"
				+ "primary key(STID))" + "ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		List list = new ArrayList();
		String sql = "select * from stuperinfo";
		try {
			Statement smt = conn.createStatement();
			ResultSet rs = smt.executeQuery(sql);
			while (rs.next()) {
				long stId = rs.getLong("stId");
				String stName = rs.getString("stName");
				String stSex = rs.getString("stSex");
				String stAge = rs.getString("stAge");
				Long stTel = rs.getLong("stTel");
				String stDept = rs.getString("stDept");
				String stAddress = rs.getString("stAddress");
				String stGroup = rs.getString("stGroup");
				Student stu = new Student(stId, stName, stSex, stAge, stTel, stDept, stAddress, stGroup);
				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查找指定的学生(更新页面操作使用)
	 * 通过学生学号对stuperinfo数据库表查找,如果找到对应学号，则将该学生信息显示出来，返回的是一个学生对象
	 * 若找不到对应学生学号，则说明并没有此学生
	 *
	 * @param id  传入一个long类型的学生学号
	 * @return stu  返回学号所对应的学生信息
	 */

	@Override
	public Student findStudentById(String id) {
		Connection conn = DbConnection.getConnection(create, false);
		Student stu = null;
		String sql = "select * from stuperinfo where stId=?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, id);
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				Long stId = rs.getLong("stId");
				String stName = rs.getString("stName");
				String stSex = rs.getString("stSex");
				String stAge = rs.getString("stAge");
				Long stTel = rs.getLong("stTel");
				String stDept = rs.getString("stDept");
				String stAddress = rs.getString("stAddress");
				String stGroup = rs.getString("stGroup");
				stu = new Student(stId, stName, stSex, stAge, stTel, stDept, stAddress, stGroup);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stu;
	}

	/**
	 * 删除学生信息<br>
	 * 通过对应的学生学号，将数据库表stuperinfo内该学生的信息全部删除
	 *
	 * @param id 传入一个long类型的学生学号
	 * @return flag flag=true时删除成功
	 */
	@Override
	public boolean delStudent(long id) {
		Connection conn = DbConnection.getConnection(create, false);
		boolean flag = false;
		String sql = "delete from stuperinfo where stId=?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setLong(1, id);
			if (psmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 添加小组信息<br>
	 * 将输入的小组信息传入数据表格projectdata，返回true时则添加成功，否则返回false
	 *
	 * @param group 传入一个Group对象
	 * @return flag flag = true则添加小组成功，false时则添加失败
	 * @see cn.edu.scau.cmi.tangqingning.model.Group
	 */
	@Override
	public boolean addProData(Group group) {
		create = "create table " + "projectdata" + "(GROUPNUMBER int(12) ZEROFILL NOT NULL COMMENT '小组编号' ,"
				+ " PROINTRODUCTION varchar(1000) DEFAULT '0' COMMENT '项目介绍',"
				+ " PROREQUIREMENT varchar(1000) DEFAULT '0' COMMENT '项目要求',"
				+ " PRODIVISION varchar(1000) DEFAULT '0' COMMENT '项目分工',"
				+ " NUMBEROFEVALUATER varchar(11) DEFAULT '0' COMMENT '被评人数',"
				+ "FUNCTIONSCORE varchar(5) DEFAULT '0' COMMENT '功能平均分',"
				+ "INTERFACESCORE varchar(5) DEFAULT '0' COMMENT '界面效果平均分',"
				+ "INNOVATIONSCORE varchar(5) DEFAULT '0' COMMENT '创新性平均分',"
				+ "ORALDEFENSESCORE varchar(5) DEFAULT '0' COMMENT '答辩表现平均分',"
				+ "AVERAGESCORE varchar(5) DEFAULT '0' COMMENT '总分'," + "primary key(GROUPNUMBER))"
				+ " ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		boolean flag = false;
		String sql = "insert into projectdata(groupNumber,proIntroduction,proRequirement,proDivision) values(?,?,?,?)";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setInt(1, group.getGroupNumber());
			psmt.setString(2, group.getProIntroduction());
			psmt.setString(3, group.getProRequirement());
			psmt.setString(4, group.getProDivision());

			int i = psmt.executeUpdate();// 返回值是一个整数，指示受影响的行数（即更新计数）
			if (i == 1) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 查询全体小组信息<br>
	 * 访问数据表projectdata，将全部小组信息显示出来，返回整个小组信息列表
	 *
	 * @return list 返回全体小组信息列表
	 */
	@SuppressWarnings("unchecked")
	public List groupSelect() {
		create = "create table " + "projectdata" + "(GROUPNUMBER int(12) ZEROFILL NOT NULL COMMENT '小组编号' ,"
				+ " PROINTRODUCTION varchar(1000) DEFAULT '0' COMMENT '项目介绍',"
				+ " PROREQUIREMENT varchar(1000) DEFAULT '0' COMMENT '项目要求',"
				+ " PRODIVISION varchar(1000) DEFAULT '0' COMMENT '项目分工',"
				+ " NUMBEROFEVALUATER varchar(11) DEFAULT '0' COMMENT '被评人数',"
				+ "FUNCTIONSCORE varchar(5) DEFAULT '0' COMMENT '功能平均分',"
				+ "INTERFACESCORE varchar(5) DEFAULT '0' COMMENT '界面效果平均分',"
				+ "INNOVATIONSCORE varchar(5) DEFAULT '0' COMMENT '创新性平均分',"
				+ "ORALDEFENSESCORE varchar(5) DEFAULT '0' COMMENT '答辩表现平均分',"
				+ "AVERAGESCORE varchar(5) DEFAULT '0' COMMENT '总分'," + "primary key(GROUPNUMBER))"
				+ " ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		List list = new ArrayList();
		String sql = "select * from projectdata";
		try {
			Statement smt = conn.createStatement();
			ResultSet rs = smt.executeQuery(sql);
			// Student(stId,stName,stSex,stAge,stTel,stDept,stAddress)
			while (rs.next()) {
				int groupNumber = rs.getInt("groupNumber");
				String proIntroduction = rs.getString("proIntroduction");
				String proRequirement = rs.getString("proRequirement");
				String proDivision = rs.getString("proDivision");
				int numberOfEvaluater = rs.getInt("numberOfEvaluater");
				double functionScore = rs.getDouble("functionScore");
				double interfaceScore = rs.getDouble("interfaceScore");
				double innovationScore = rs.getDouble("innovationScore");
				double oralDefenseScore = rs.getDouble("oralDefenseScore");
				double averageScore = rs.getDouble("averageScore");
				Group group = new Group(groupNumber, proIntroduction, proRequirement, proDivision, numberOfEvaluater,
						functionScore, interfaceScore, innovationScore, oralDefenseScore, averageScore);
				list.add(group);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 更新小组信息<br>
	 * 读取出原来的数据并且更新新的小组信息进数据库表projectdata
	 *
	 * @param group 传入一个小组对象
	 * @return flag flag = true时则更新小组信息成功，否则返回false
	 * @see cn.edu.scau.cmi.tangqingning.model.Group
	 */
	@Override
	public boolean updateGroup(Group group) {
		Connection conn = DbConnection.getConnection(create, false);
		boolean flag = false;
		String sql = "update projectdata set proIntroduction=?,proRequirement=?,proDivision=? where groupNumber=? ";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, group.getProIntroduction());
			psmt.setString(2, group.getProRequirement());
			psmt.setString(3, group.getProDivision());
			psmt.setLong(4, group.getGroupNumber());

			int i = psmt.executeUpdate();
			if (i == 1) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 查找指定组号小组信息<br>
	 * 通过小组组号，访问数据库表projectdata，查找到对应的小组号，则返回该小组对象，将相应的小组信息显示出来
	 *
	 * @param groupnumber 传入一个String类型的小组号
	 * @return group 返回一个小组对象
	 */

	@Override
	public Group findGroupBygGroupNumber(String groupnumber) {
		Connection conn = DbConnection.getConnection(create, false);
		Group group = null;
		String sql = "select * from projectdata where groupNumber=?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, groupnumber);
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				String groupNumber = rs.getString("groupNumber");
				String proIntroduction = rs.getString("proIntroduction");
				String proRequirement = rs.getString("proRequirement");
				String proDivision = rs.getString("proDivision");
				group = new Group(Integer.parseInt(groupNumber), proIntroduction, proRequirement, proDivision);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return group;
	}

	/**
	 * 删除小组信息<br>
	 * 通过对应的小组号，将数据库表projectdata内该小组的信息全部删除<br>
	 * 先把有该组号的学生查询出来，删除某个小组后再根据list学生组，更新它们的组号
	 *
	 * @param groupNumber 传入一个String类型的小组号
	 * @return flag flag = true时则删除成功，否则返回false
	 */
	@SuppressWarnings("unchecked")
	@Override
	public boolean delGroup(int groupNumber) {
		Connection conn = DbConnection.getConnection(create, false);
		boolean flag = false;
		String sql = "delete from projectdata where groupNumber=?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setLong(1, groupNumber);
			if (psmt.executeUpdate() > 0) {

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 先把有该组号的学生查询出来。
		List<Student> list = null;
		try {
			sql = "select * from stuperinfo where stGroup=?";
			list = new ArrayList();
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setLong(1, groupNumber);
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				Student stu = new Student();
				stu.setStId(rs.getLong("stId"));
				stu.setStGroup("0");
				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 再根据list学生组，更新它们的组号
		try {
			int index = 0;
			for (index = 0; index < list.size(); index++) {
				sql = "update stuperinfo set stGroup=? where stId=?";
				PreparedStatement psmt = conn.prepareStatement(sql);
				psmt.setInt(1, Integer.parseInt(list.get(index).getStGroup()));
				psmt.setLong(2, list.get(index).getStId());
				int i = psmt.executeUpdate();
				if (i != 1) {
					flag = false;
					continue;
				}
			}
			if (index == list.size()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

}
